﻿unit EventGrRepFrm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs,  DBGridEh, DB, FIBDataSet, pFIBDataSet, JvAppStorage,
  cxGridExportLink,DateUtils,
  dxRibbonForm, DBGridEhImpExp,
  JvAppIniStorage, JvComponentBase, JvFormPlacement, GridsEh, ExtCtrls,
  StdCtrls, Mask, DBCtrlsEh, DBLookupEh, ComCtrls, JvExControls, JvXPCore,
  JvXPBar, MemTableDataEh, MemTableEh, FIBQuery, pFIBCacheQueries, dxSkinsCore,
  dxSkinMcSkin, dxSkinSeven, dxSkinsdxRibbonPainter, cxClasses, dxRibbon,
  cxControls, JvDialogs, cxGraphics, cxLookAndFeels, cxLookAndFeelPainters,
  dxSkinOffice2010Black, dxSkinOffice2010Blue, dxSkinOffice2010Silver;

type
  TEventGrRepForm = class(Tdxcustomribbonform)
    Panel1: TPanel;
    JvXPBar1: TJvXPBar;
    Panel2: TPanel;
    GroupBox1: TGroupBox;
    Label2: TLabel;
    Label3: TLabel;
    D1: TDateTimePicker;
    D2: TDateTimePicker;
    Kontragent: TDBLookupComboboxEh;
    IsPrincipal: TCheckBox;
    Collector: TDBLookupComboboxEh;
    IsCollector: TCheckBox;
    RType: TComboBox;
    JvXPBar2: TJvXPBar;
    JvXPBar3: TJvXPBar;
    IsActive: TCheckBox;
    Splitter1: TSplitter;
    Grid: TDBGridEh;
    JvFormStorage1: TJvFormStorage;
    JvAppIniFileStorage1: TJvAppIniFileStorage;
    KSource: TDataSource;
    KDS: TpFIBDataSet;
    CDS: TpFIBDataSet;
    CSource: TDataSource;
    StatusBar: TStatusBar;
    DS: TpFIBDataSet;
    T1: TMemTableEh;
    T2: TMemTableEh;
    DataSource: TDataSource;
    RibbonTab1: TdxRibbonTab;
    Ribbon: TdxRibbon;
    dlg1: TJvSaveDialog;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure JvXPBar2Click(Sender: TObject);
    procedure JvXPBar3Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
    StartTime: TDateTime;
    EList: TStrings;
    function EFName(S: string): string;
    function GetEShort(S: string): string;
    procedure SetMsg(Num: integer; Msg: string = '');
    procedure OpenDataSet;
    procedure AddEList(EType: string);
    procedure CreateT1;
    procedure ScanData;
    procedure AddRecord;
    procedure CreateT2;
    procedure MakeT2;
  public
   procedure MakeReport;
  end;

var
  EventGrRepForm: TEventGrRepForm;

implementation
 uses  NameFrm, SrvSaveListFrm, {GridToXLS,} SrvFltUnit,
       DModule, cmUtils;

{$R *.dfm}

procedure TEventGrRepForm.AddEList(EType: string);
begin
 if EList.IndexOf(EType)<0 then  EList.Add(EType);
end;

procedure TEventGrRepForm.FormActivate(Sender: TObject);
begin
  SetTabActive('rep',True);
end;

procedure TEventGrRepForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
 RemoveBtn(Name);
 EList.Free;
 Action:=caFree;
end;

procedure TEventGrRepForm.FormCreate(Sender: TObject);
begin
 AddButton(self);
 EList:=TStringList.Create;
 KDS.Open; CDS.Open;
 Kontragent.SelectNextValue(true);
 Collector.SelectNextValue(true);
end;

function TEventGrRepForm.GetEShort(S: string): string;
 var Q: TFIBQuery;
begin
 Q:=GetQueryForUse(dm1.trnRD, 'select ShortName from spr$EType where Code=:Code');
 Q.ParamByName('Code').AsString:=S;
 Q.ExecQuery;
 Result:=Q.FieldByName('ShortName').AsString;
 if Result='' then Result:=S;
 FreeQueryForUse(Q);
end;

procedure TEventGrRepForm.JvXPBar2Click(Sender: TObject);
var
  day :Integer;
begin
 day:= DaysBetween(d1.Date, d2.Date);
 if day>60 then
 begin
  if MessageDlg('Вы действительно хотите сформировать отчет за' + #13#10 +
                '                '+DateName(day)+
                ' ?',  mtWarning, [mbYes, mbNo], 0) = mrYes then
  begin
     MakeReport;
  end else Exit;
 end else MakeReport;
end;

procedure TEventGrRepForm.JvXPBar3Click(Sender: TObject);
begin
 //SaveAsExcel(Grid, nil, '');
 if dlg1.Execute then
 SaveDBGridEhToExportFile(TDBGridEhExportAsXLS,
                          Grid,dlg1.FileName+'.xls',true);
end;

procedure TEventGrRepForm.SetMsg(Num: integer; Msg: string);
begin
 with StatusBar do
  begin
   Panels[0].Text:=TimeToStr(Time-StartTime);
   if Msg<>'' then Panels[2].Text:=Msg;
   if Num=0 then Panels[1].Text:='' else Panels[1].Text:=IntToStr(Num);
   Application.ProcessMessages;
  end;
end;

procedure TEventGrRepForm.CreateT1;
begin
 T1.Close;
 with T1.FieldDefs do
  begin
   Clear;
   Add('Data',ftDate);
   Add('EType',ftString,32);
   Add('ECount',ftInteger);
   Add('Summa',ftFloat);
  end;
 T1.CreateDataSet;
 T1.Open;
end;

function TEventGrRepForm.EFName(S: string): string;
 var i: integer;
begin
 Result:='F';
 for i:=1 to Length(S) do
  if S[i]='-' then Result:=Result+'_'
              else Result:=Result+S[i];
end;

procedure TEventGrRepForm.CreateT2;
 var i: integer;
     C: TColumnEh;
     FieldName: string;
begin
 T2.Close;
 Grid.Columns.Clear;
 with T2.FieldDefs do
  begin
   Clear;
   //дата мероприятий
   Add('Data', ftDate);
   C:=Grid.Columns.Add;
   C.Title.Caption:='Дата';
   C.FieldName:='Data';
   C.Footer.ValueType:=fvtCount;
   //список мероприятий
   for i:=0 to EList.Count - 1 do
    begin
     FieldName:=EFName(EList[i]);
     Add(FieldName,ftInteger);
     C:=Grid.Columns.Add;
     C.Title.Caption:='Мероприятия|'+GetEShort(EList[i]);
     C.FieldName:=FieldName;
     C.Footer.ValueType:=fvtSum;
    end;
   Add('Itog',ftInteger);
   C:=Grid.Columns.Add;
   C.Title.Caption:='Мероприятия|Всего';
   C.FieldName:='Itog';
   C.Footer.ValueType:=fvtSum;
   //сумма обещанного платежа
   Add('OSumma', ftCurrency);
   C:=Grid.Columns.Add;
   C.Width:=C.Width+20;
   C.Title.Caption:='Обещанный платеж';
   C.DisplayFormat:='#,##0.00';
   C.FieldName:='OSumma';
   C.Footer.ValueType:=fvtSum;
   C.Footer.DisplayFormat:='#,##0.00';
   //сумма платежей за период
   Add('Summa', ftCurrency);
   C:=Grid.Columns.Add;
   C.Width:=C.Width+20;
   C.Title.Caption:='Оплаты';
   C.DisplayFormat:='#,##0.00';
   C.FieldName:='Summa';
   C.Footer.ValueType:=fvtSum;
   C.Footer.DisplayFormat:='#,##0.00';
  end;
 T2.CreateDataSet;
 T2.Open;
end;

procedure TEventGrRepForm.AddRecord;
begin
 //добавляем в список код мероприятия
 AddEList(DS.FN('EType').AsString);
 with T1 do
  begin
   Append;
   FieldByName('Data').AsString:=DateToStr(DS.FN('D').AsDateTime);
   FieldByName('EType').AsString:=DS.FN('EType').AsString;
   FieldByName('ECount').AsInteger:=DS.FN('ECount').AsInteger;
   FieldByName('Summa').AsCurrency:=DS.FN('Summa').AsCurrency;
   Post;
  end;
end;

procedure TEventGrRepForm.ScanData;
 var i: integer;
begin
 SetMsg(0,'Сканирование данных....');
 CreateT1;
 OpenDataSet; //выбираем данные для отчета
 try
  i:=0;
  while not DS.Eof do
   begin
    AddRecord;
    DS.Next;
    Application.ProcessMessages;
    Inc(i); SetMsg(i);
   end;
 finally
  DS.Close;
 end;
end;

procedure TEventGrRepForm.MakeT2;
 var i: integer;
     OldData: TDateTime;
     Q: TFIBQuery;

 procedure PostRecord;
  begin
   if not (T2.State in [dsEdit, dsInsert]) then
     Exit;
   Q.ParamByName('D').AsDateTime:=T2.FieldByName('Data').AsDateTime;
   Q.ExecQuery;
   T2.FieldByName('Summa').AsCurrency:=Q.FN('Summa').AsCurrency*-1;
   T2.Post;
  end;

 procedure SetFields;
  var FieldName: string;
  begin
   FieldName:=EFName(T1.FieldByName('EType').AsString);
   T2.FieldByName(FieldName).AsInteger:=T2.FieldByName(FieldName).AsInteger+
                                        T1.FieldByName('ECount').AsInteger;
   T2.FieldByName('Itog').AsInteger:=T2.FieldByName('Itog').AsInteger+
                                        T1.FieldByName('ECount').AsInteger;
   T2.FieldByName('OSumma').AsCurrency:=T2.FieldByName('OSumma').AsCurrency+
                                        T1.FieldByName('Summa').AsCurrency;
  end;

 procedure NewRecord;
  begin
   T2.Append;
   T2.FieldByName('Data').AsDateTime:=T1.FieldByName('Data').AsDateTime;
   SetFields;
   OldData:=T1.FieldByName('Data').AsDateTime;
  end;

 procedure EditRecord;
  begin
   SetFields;
  end;

begin
 SetMsg(0,'Формирование отчета....');
 CreateT2; //создаем таблицу для приема данных
 //запрос на выбор данных
 Q:=GetQueryForUse(dm1.trnRD, 'select Summa from GetDayOplata(:D, :K, :C)');
 try
  //общие параметры запроса
  if IsPrincipal.Checked then
   Q.ParamByName('K').AsString:=KDS.FN('Code').AsString
  else
   Q.ParamByName('K').AsString:='';
  if IsCollector.Checked then
   Q.ParamByName('C').AsString:=CDS.FN('Code').AsString
  else
   Q.ParamByName('C').AsString:='0';
  i:=0;
  OldData:=StrToDate('1.1.1900');
  Grid.DataSource:=nil;
  T1.First;
  while not T1.Eof do
   begin
    if OldData<>T1.FieldByName('Data').AsDateTime then
     begin
      PostRecord;
      NewRecord;
     end else EditRecord;
    Application.ProcessMessages;
    T1.Next;
    Inc(i); SetMsg(i);
   end;
  PostRecord; //последняя запись набора данных
  Grid.DataSource:=DataSource;
 finally
  FreeQueryForUse(Q);
 end;
end;

procedure TEventGrRepForm.MakeReport;
begin
 EList.Clear;
 StartTime:=Now;
  Screen.Cursors[5]:=LoadCursorFromFile
                  (PWideChar(ExtractFilePath(Application.ExeName)+'common\busy.ani'));
  Screen.Cursor := 5;
  Application.ProcessMessages;
 ScanData; //получаем из базы данные для отчета
 MakeT2; //формируем отчет
  Screen.Cursor:=crDefault;
 SetMsg(0,'Отчет сформирован.');
end;

procedure TEventGrRepForm.OpenDataSet;
begin
 DS.Close;
 DS.ParamByName('D1').AsDate:=D1.Date;
 DS.ParamByName('D2').AsDate:=D2.Date;
 if IsPrincipal.Checked then
   DS.ParamByName('Kontragent').AsString:=KDS.FN('Code').AsString
 else
   DS.ParamByName('Kontragent').AsString:='';
 if IsCollector.Checked then
   DS.ParamByName('Collector').AsString:=CDS.FN('Code').AsString
 else
   DS.ParamByName('Collector').AsInteger:=0;
 DS.ParamByName('RepType').AsInteger:=RType.ItemIndex;
 DS.ParamByName('RType').AsInteger:=Ord(IsActive.Checked);
 DS.Open;
end;

end.
